# coding: utf-8

import time
import requests
import pymysql

from log import logger

url = 'http://i.sporttery.cn/api/fb_match_info/get_pool_rs/?mid=%s'
HEADER = {
    'User-Agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_6) AppleWebKit/537.36 '
    '(KHTML, like Gecko) Chrome/60.0.3112.90 Safari/537.36'
}

def search_instant_match_id():
    logger.info('call search_instant_match_id')
    logger.info('Mysql DB connect...')
    db = pymysql.connect("120.92.172.149", "root", "XdrgBnjI87", "lottery", use_unicode=True, charset="utf8")
    cursor = db.cursor()
    cursor.execute("SELECT DISTINCT(`match_id`) FROM `tb_instant_match`")
    match_ids = cursor.fetchall()
    cursor.close()
    return match_ids

def get_finish_match_info(mid):
    logger.info('call get_finish_match_info')
    while True:
        try:
            n_url = url % mid
            logger.info('Get URL:%s' % n_url)
            r = requests.get(n_url, headers=HEADER)
            break
        except Exception as ex:
            time.sleep(5)
            pass

    return r.json()['result']


def save_match(mid, datas):
    logger.info('call save_match')
    if len(datas['pool_rs']) > 0:
        logger.info('Mysql DB connect...')
        db = pymysql.connect("120.92.172.149", "root", "XdrgBnjI87", "lottery", use_unicode=True, charset="utf8")
        cursor = db.cursor()
        cursor.execute("SELECT * FROM `tb_instant_match` WHERE match_id='%s' ORDER by create_time desc;" % mid[0])
        match_info = cursor.fetchone()
        print(match_info)

        # 同步处理
        scores = datas['pool_rs']['crs']['prs_name'].split(':')
        status = '完'
        home_score = scores[0]
        away_score = scores[1]

        if 'had' in datas['pool_rs']:
            had_result = datas['pool_rs']['had']['pool_rs']
        else:
            had_result = ''

        if 'hhad' in datas['pool_rs']:
            hhad_result = datas['pool_rs']['hhad']['pool_rs']
        else:
            hhad_result = ''

        if had_result == 'h':
            match_result = 3
        elif had_result == 'd':
            match_result = 1
        elif had_result == 'a':
            match_result = 0
        else:
            match_result = -1

        if hhad_result == 'h':
            match_result2 = 3
        elif hhad_result == 'd':
            match_result2 = 1
        elif hhad_result == 'a':
            match_result2 = 0
        else:
            match_result2 = -1

        insertSql = """INSERT INTO tb_match
              (`match_id`, `type`, `date`, `home_id`, `home_name`, `home_rank`, `away_id`, `away_name`, `away_rank`, `rate_win`, `rate_draw`, `rate_lose`, `let_ball`, `rate_win_2`, `rate_draw_2`, `rate_lose_2`, `status`, `home_score`, `away_score`, `match_result`, `match_result_2`)
              VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')""" % (match_info[1], match_info[2], match_info[3], match_info[4], match_info[5], match_info[6], match_info[7], match_info[8], match_info[9], match_info[10], match_info[11], match_info[12], match_info[13], match_info[14], match_info[15], match_info[16],status, home_score, away_score, match_result, match_result2)
        deleteSql = 'DELETE FROM `tb_instant_match` WHERE MATCH_id = \'%s\'' % mid[0]
        try:
            logger.info('execue SQL:%s' % insertSql)
            logger.info('execue SQL:%s' % deleteSql)
            # 执行sql语句
            cursor.execute(insertSql)
            cursor.execute(deleteSql)
            # 提交到数据库执行
            db.commit()
        except Exception as e:
            logger.error(e)
            # 如果发生错误则回滚
            db.rollback()
        cursor.close()


def main():
    mids = search_instant_match_id()
    for mid in mids:
        datas = get_finish_match_info(mid[0])
        save_match(mid, datas)

if __name__ == '__main__':
    logger.info('start crawl')
    main()
